from apscheduler.schedulers.blocking import BlockingScheduler
import traceback
import pymysql
import baostock as bs
import pandas as pd
from decimal import Decimal
import math
import datetime

from util.daylink import daylink


def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    return False


#每日数据更新
def  sql():
     print('基础数据写入')
     lg = bs.login(user_id='anonymous', password='123456')
     # 打开数据库连接
     db = pymysql.connect(host='101.35.200.120', port=3306, user='root', passwd='ljf123456', db='gp', charset='utf8')
     # 使用cursor()方法获取操作游标
     cursor = db.cursor()
     # SQL 查询语句
     sql = "select * from shares"
     try:
          # 执行SQL语句
          cursor.execute(sql)
          # 获取所有记录列表
          results = cursor.fetchall()
          for row in results:
               id = row[0]
               code = row[1]
               name = row[2]
               regionCode = row[5]
               # 获取最后日期 ALTER TABLE trade_history ADD INDEX index_name ( time )
               sSql = """select id, time_val from record_data_%s  where  code = '%s'  order by time desc limit 1 """ \
                      % (code, code)
               cursor.execute(sSql)
               sResults = cursor.fetchall()
               if (len(sResults) > 0):
                    for row in sResults:
                         # data = ((datetime.datetime.strptime(row[2], '%Y-%m-%d')).timedelta(days=1)).strftime('%Y-%m-%d')
                         in_date = row[1]
                         if (in_date == None):
                              data = '1990-01-01'
                         else:
                              dt = datetime.datetime.strptime(in_date, "%Y-%m-%d")
                              # if(dt>datetime.datetime.now()):
                              data = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")

               else:
                    data = '1990-01-01'
               # print(data)
               if (datetime.datetime.strptime(data, "%Y-%m-%d") <= datetime.datetime.now()):
                    rs = bs.query_history_k_data_plus(regionCode,
                                                      "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",
                                                      start_date=data, end_date='%s-%s-%s' % (
                         datetime.datetime.now().year, datetime.datetime.now().month, datetime.datetime.now().day),
                                                      frequency="d",
                                                      adjustflag="3")
                    # frequency="d"取日k线，adjustflag="3"复权类型，默认不复权：3；1：后复权；2：前复权
                    #### 打印结果集 ####
                    data_list = []
                    while (rs.error_code == '0') & rs.next():
                         li = rs.get_row_data()
                         openingPrice = li[2];
                         openingPrice = openingPrice if is_number(openingPrice) else 0
                         # is_number(openingPrice)
                         # openingPrice=is_number(openingPrice)==True?openingPrice:0;
                         maxPrice = li[3];
                         maxPrice = maxPrice if is_number(maxPrice) else 0
                         minPrice = li[4];
                         minPrice = minPrice if is_number(minPrice) else 0
                         closePrice = li[5];
                         closePrice = closePrice if is_number(closePrice) else 0
                         preclose = li[6];
                         preclose = preclose if is_number(preclose) else 0
                         turnover = li[7];
                         turnover = turnover if is_number(turnover) else 0
                         turnover = float(turnover)
                         turnover = math.trunc(turnover)
                         turnoverAmount = li[8];
                         turnoverAmount = turnoverAmount if is_number(turnoverAmount) else 0
                         turnoverAmount = float(turnoverAmount)
                         turnoverAmount = math.trunc(turnoverAmount)
                         turnoverRate = li[10];
                         turnoverRate = turnoverRate if is_number(turnoverRate) else 0
                         tradestatus = li[11];
                         tradestatus = tradestatus if is_number(tradestatus) else 0
                         todayRange = li[12];
                         todayRange = todayRange if is_number(todayRange) else 0
                         peTtm = li[13];
                         peTtm = peTtm if is_number(peTtm) else 0
                         pbMrq = li[14];
                         pbMrq = pbMrq if is_number(pbMrq) else 0
                         psTtm = li[15];
                         psTtm = psTtm if is_number(psTtm) else 0
                         pcfNcfTtm = li[16];
                         pcfNcfTtm = pcfNcfTtm if is_number(pcfNcfTtm) else 0
                         isSt = li[17];
                         isSt = isSt if is_number(isSt) else 0
                         sql1 = """INSERT INTO record_data_%s (time,time_val, region_code, opening_price, max_price, min_price, close_price, preclose, turnover,
                             turnover_amount, turnover_rate, tradestatus, today_range, pe_ttm, pb_mrq, ps_ttm, pcf_ncf_ttm,
                              is_st,name,code) VALUES ('%s','%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') """ \
                                % (code, li[0], li[0], li[1], openingPrice, maxPrice, minPrice, closePrice, preclose,
                                   turnover, turnoverAmount, turnoverRate, tradestatus, todayRange, peTtm, pbMrq, psTtm,
                                   pcfNcfTtm, isSt, name, code)
                         try:

                              cursor.execute(sql1)
                              # 执行sql语句
                              db.commit()
                         except Exception as e:
                              # print(e)
                              #f = open('D:/except.txt', 'a')
                              #f.write('\n' + sql1)
                              # 发生错误时回滚
                              db.rollback()

     except Exception as e:
          traceback.print_exc()
          print("Error: unable to fecth data")

     finally:
          try:
               # 关闭数据库连接
               db.close()
               bs.logout()
          except Exception as e:
               traceback.print_exc()
               print("Error: unable to fecth data")

def  fqyz():
     print('复权数据写入')
     # 登陆系统
     lg = bs.login()
     # 打开数据库连接
     db = pymysql.connect(host='101.35.200.120', port=3306, user='root', passwd='ljf123456', db='gp', charset='utf8')
     # 使用cursor()方法获取操作游标
     cursor = db.cursor()
     # SQL 查询语句
     sql = "select * from shares "
     try:
          # 执行SQL语句
          cursor.execute(sql)
          # 获取所有记录列表
          results = cursor.fetchall()
          for row in results:
               id = row[0]
               code = row[1]
               name = row[2]
               regionCode = row[5]
               # 查询2015至2017年复权因子
               rs_list = []
               rs_factor = bs.query_adjust_factor(code=regionCode, start_date="1990-01-01", end_date='%s-%s-%s' % (
               datetime.datetime.now().year, datetime.datetime.now().month, datetime.datetime.now().day))
               while (rs_factor.error_code == '0') & rs_factor.next():
                    li = rs_factor.get_row_data()
                    dividOperateDate = li[1]
                    foreAdjustFactor = li[2]
                    backAdjustFactor = li[3]
                    sql1 = """INSERT INTO adjust_factor(code, region_code, name, divid_operate_date, operate_date, fore_adjust_factor, back_adjust_factor) VALUES ('%s', '%s','%s','%s','%s','%s','%s') """ \
                           % (code, regionCode, name, dividOperateDate, dividOperateDate, foreAdjustFactor,
                              backAdjustFactor)
                    try:
                         selectSql = """select * from adjust_factor  WHERE  code = '%s'  AND operate_date = '%s' """ \
                                     % (code, dividOperateDate)
                         cursor.execute(selectSql)
                         selectResults = cursor.fetchall()
                         if (len(selectResults) == 0):
                              cursor.execute(sql1)
                              # 执行sql语句
                         else:
                              id = row[0]
                              sql1 = """UPDATE adjust_factor SET   fore_adjust_factor = %s  and back_adjust_factor = %s  where  id = %s """ % (
                              foreAdjustFactor, backAdjustFactor, id)
                              cursor.execute(sql1)
                         db.commit()
                    except Exception as e:
                         # 发生错误时回滚
                         db.rollback()
     except Exception as e:
          traceback.print_exc()
          print("Error: unable to fecth data")

     finally:
          try:
               # 关闭数据库连接
               db.close()
               bs.logout()
          except Exception as e:
               traceback.print_exc()
               print("Error: unable to fecth data")



if __name__ == '__main__':




     scheduler = BlockingScheduler()
     # mon-fri： 周一到周五，也可以写成 1-5 ，时间 18:30 ，ars：方法 notify入参；每周-到周五下午六点半执行notify方法
     scheduler.add_job(sql, 'cron', day_of_week='mon-fri',misfire_grace_time=3600,  hour=18, minute=10)
     scheduler.add_job(fqyz, 'cron',month='4,5,7,8,10,11,1,2',misfire_grace_time=3600, day='1', hour=8, minute=5)

     #scheduler.add_job(sql, 'cron', day_of_week='mon-fri', hour=8, minute=59)
     #scheduler.add_job(fqyz, 'cron',month='4,5,7,8,10,11,1,2', day='1', hour=8, minute=5)

     scheduler.start()